﻿IF NOT EXISTS (SELECT NULL FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[medianamik_GetUsersByGroupsCount]') AND type in (N'P', N'PC'))
BEGIN
declare @sql nvarchar(4000), @guid varchar(50), @culture varchar(10)
SET @guid = '53D05866-F126-4875-A658-C8054CFDAEE2'
SET @culture = ''

set @sql = 'ALTER VIEW [dbo].[vw_UserProfile]
                    AS
                    SELECT p.*, u.[UserId], u.[Name], u.[LastLockedOutDate], u.[IsLockedOut], u.[PasswordQuestion], u.[PasswordAnswer], u.[Comment], u.[LastPasswordChangedDate], u.[IsApproved], u.[LastActivityDate], u.[Email], u.[Password], u.[Salt], u.[PasswordFormat]
                    FROM dbo.vw_gen_MedianamikProfile AS p INNER JOIN
                        dbo.medianamik_User AS u ON p.[User] = u.UserId'
exec sp_executesql @sql

set @sql = 'CREATE PROCEDURE [dbo].[medianamik_GetUsersByGroupsCount]
(
	@Groups varchar(2000),
	@keywords varchar(2000),
	@IsActive bit
)
AS

SELECT	count(*)
from	[dbo].[vw_UserProfile]  as up
		inner join (SELECT	up.nodeid, min(g.name) GroupName
					FROM	[dbo].[vw_UserProfile] up
							left JOIN [dbo].[medianamik_UserInGroup] uig ON up.[UserId] = uig.[UserId]
							left JOIN [dbo].[medianamik_Group] g ON uig.[GroupId] = g.[GroupId]
							WHERE up.[Generation] = -1
							AND (@isactive is null or up.isactive = @IsActive)
							AND up.[CultureName] = ''' + @culture + '''
							AND up.[ParentNodeId] = ''' + @guid + '''
							and (@Groups is null or g.name in (select item from Split(@Groups,'','')))
							and (@keywords is null or up.Name LIKE @keywords OR up.Email like @keywords)
							group by up.nodeid) as bobby on bobby.nodeid = up.nodeid'

exec sp_executesql @sql
GRANT EXECUTE ON [dbo].[medianamik_GetUsersByGroupsCount] TO [Medianamik_DataAccess]
END


IF NOT EXISTS (SELECT NULL FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[medianamik_GetUsersByGroups]') AND type in (N'P', N'PC'))
BEGIN
EXEC('CREATE PROCEDURE medianamik_GetUsersByGroups
(
	@Groups varchar(2000),
	@PageSize int,
	@PageIndex int,
	@keywords varchar(2000),
	@IsActive bit
)
AS

SELECT	*
FROM	(SELECT ROW_NUMBER() OVER ( ORDER BY up.[Name] ASC) AS Row, 
		up.*
from	[dbo].[vw_UserProfile]  as up
		inner join (SELECT	up.nodeid, min(g.name) GroupName
					FROM	[dbo].[vw_UserProfile] up
							left JOIN [dbo].[medianamik_UserInGroup] uig ON up.[UserId] = uig.[UserId]
							left JOIN [dbo].[medianamik_Group] g ON uig.[GroupId] = g.[GroupId]
							WHERE up.[Generation] = -1
							AND (@isactive is null or up.isactive = @IsActive)
							AND up.[CultureName] = ''''
							AND up.[ParentNodeId] = ''53D05866-F126-4875-A658-C8054CFDAEE2''
							and (@Groups is null or g.name in (select item from Split(@Groups,'','')))
							and (@keywords is null or up.Name LIKE @keywords OR up.Email like @keywords)
							group by up.nodeid) as bobby on bobby.nodeid = up.nodeid
					WHERE up.[Generation] = -1
					) AS PagedResults
WHERE	Row >= (@PageIndex - 1) * @PageSize + 1 AND Row <= @PageIndex * @PageSize
ORDER BY name')
GRANT EXECUTE ON [dbo].[medianamik_GetUsersByGroups] TO [Medianamik_DataAccess]
END